#! /usr/bin/env python
# -*- coding: utf-8 -*-
# __author__ = "Q1mi"
# Date: 2018/10/30
import pymssql

class Sql(object):
    #sql_pre：sql语句，初始化
    def __init__(self,sql_tabel="istrong_data_collection",server = "119.29.146.108",user= "sa",password = "S3686-e342qq-3686e342"):
       # self.sql_pre = sql_pre
        self.server =server
        self.user =user
        self.password = password
        self.table = sql_tabel
    #获取sql链接
    def Obtain_connect(self):
        connect = pymssql.connect(self.server, self.user, self.password, self.table, charset="utf8")  # 获取连接
        cursor = connect.cursor()  # 获取光标
        return cursor,connect

    #查询sql
    def Obtain_sql(self,sql_pre):
        cursor, connect = self.Obtain_connect()  # 获取光标

        cursor.execute(sql_pre)
        number_pre = cursor.fetchall()
        cursor.close()
        connect.close()
        return number_pre

    #将查询的sql转化为dataframe
    def dataframe_sql(self,sql_pre,sql_str):
        number_pre = self.Obtain_sql(sql_pre)
        columns = [x for x in sql_str.split(',')]
        weater_data_pre = pd.DataFrame(number_pre, columns=columns)
        return weater_data_pre

    #插入sql
    def Insert_sql(self,sql_pre):
        cursor, connect = self.Obtain_connect()  # 获取光标

        cursor.execute(sql_pre)
        connect.commit()
        connect.close()
